"""
数据库迁移管理器

管理数据库结构的版本和迁移。
"""

import os
from typing import Dict, Any, List, Optional
from pathlib import Path
from datetime import datetime
import logging

from sqlalchemy import text, MetaData, Table, Column, Integer, String, DateTime

from src.core.di import Injectable, Inject


@Injectable(scope="singleton")
class MigrationManager:
    """
    数据库迁移管理器
    
    管理数据库结构的版本控制和迁移。
    """
    
    def __init__(self,
                 config: Dict[str, Any] = Inject("config"),
                 logger: logging.Logger = Inject("logger")):
        self.config = config
        self.logger = logger
        
        # 迁移配置
        self.migration_config = config.get("database", {}).get("migrations", {})
        self.migrations_dir = Path(self.migration_config.get("directory", "migrations"))
        self.auto_migrate = self.migration_config.get("auto_migrate", True)
        
        # 确保迁移目录存在
        self.migrations_dir.mkdir(parents=True, exist_ok=True)
        
        # 当前数据库版本
        self.current_version = 1
        self.target_version = self._get_latest_migration_version()

        # 如果没有迁移文件，设置目标版本为2（包含剪映模板表）
        if self.target_version == 1:
            self.target_version = 2
    
    def _get_latest_migration_version(self) -> int:
        """获取最新的迁移版本号"""
        try:
            migration_files = list(self.migrations_dir.glob("*.sql"))
            if not migration_files:
                return 1
            
            versions = []
            for file in migration_files:
                try:
                    # 假设文件名格式为: 001_initial.py
                    version_str = file.stem.split('_')[0]
                    versions.append(int(version_str))
                except (ValueError, IndexError):
                    continue
            
            return max(versions) if versions else 1
            
        except Exception as e:
            self.logger.error(f"获取迁移版本失败: {e}")
            return 1
    
    def create_migration_table(self, engine) -> None:
        """创建迁移记录表"""
        try:
            metadata = MetaData()
            
            migration_table = Table(
                'schema_migrations',
                metadata,
                Column('id', Integer, primary_key=True),
                Column('version', Integer, unique=True, nullable=False),
                Column('name', String(255), nullable=False),
                Column('applied_at', DateTime, default=datetime.utcnow),
                Column('checksum', String(64), nullable=True)
            )
            
            metadata.create_all(engine)
            
        except Exception as e:
            self.logger.error(f"创建迁移记录表失败: {e}")
            raise
    
    def get_applied_migrations(self, session) -> List[int]:
        """获取已应用的迁移版本列表"""
        try:
            result = session.execute(text("SELECT version FROM schema_migrations ORDER BY version"))
            return [row[0] for row in result.fetchall()]
        except Exception:
            # 如果表不存在，返回空列表
            return []
    
    def record_migration(self, session, version: int, name: str, checksum: str = None) -> None:
        """记录已应用的迁移"""
        try:
            session.execute(
                text("INSERT INTO schema_migrations (version, name, applied_at, checksum) VALUES (:version, :name, :applied_at, :checksum)"),
                {
                    "version": version,
                    "name": name,
                    "applied_at": datetime.utcnow(),
                    "checksum": checksum
                }
            )
            session.commit()
            self.logger.info(f"迁移记录已保存: {version} - {name}")
            
        except Exception as e:
            session.rollback()
            self.logger.error(f"记录迁移失败: {e}")
            raise
    
    def apply_migration(self, session, version: int, migration_sql: str, name: str) -> None:
        """应用单个迁移"""
        try:
            self.logger.info(f"应用迁移: {version} - {name}")
            
            # 执行迁移SQL
            for statement in migration_sql.split(';'):
                statement = statement.strip()
                if statement:
                    session.execute(text(statement))
            
            # 记录迁移
            self.record_migration(session, version, name)
            
            self.logger.info(f"迁移应用成功: {version}")
            
        except Exception as e:
            session.rollback()
            self.logger.error(f"应用迁移失败 {version}: {e}")
            raise
    
    def migrate_to_latest(self, engine) -> Dict[str, Any]:
        """迁移到最新版本"""
        from .manager import get_session
        
        try:
            # 创建迁移表（如果不存在）
            self.create_migration_table(engine)
            
            session = get_session()
            
            # 获取已应用的迁移
            applied_migrations = self.get_applied_migrations(session)
            
            # 获取需要应用的迁移
            pending_migrations = []
            for version in range(1, self.target_version + 1):
                if version not in applied_migrations:
                    pending_migrations.append(version)
            
            if not pending_migrations:
                return {
                    "success": True,
                    "current_version": max(applied_migrations) if applied_migrations else 0,
                    "target_version": self.target_version,
                    "applied_migrations": []
                }
            
            # 应用待处理的迁移
            applied_count = 0
            for version in pending_migrations:
                migration_info = self._get_migration_info(version)
                if migration_info:
                    self.apply_migration(
                        session,
                        version,
                        migration_info["sql"],
                        migration_info["name"]
                    )
                    applied_count += 1
            
            session.close()
            
            result = {
                "success": True,
                "current_version": self.target_version,
                "target_version": self.target_version,
                "applied_migrations": pending_migrations,
                "applied_count": applied_count
            }
            
            self.logger.info(f"数据库迁移完成: 应用了 {applied_count} 个迁移")
            return result
            
        except Exception as e:
            self.logger.error(f"数据库迁移失败: {e}")
            return {
                "success": False,
                "error": str(e),
                "current_version": 0,
                "target_version": self.target_version
            }
    
    def _get_migration_info(self, version: int) -> Optional[Dict[str, Any]]:
        """获取迁移信息"""
        try:
            # 查找迁移文件
            migration_files = list(self.migrations_dir.glob(f"{version:03d}_*.sql"))
            
            if not migration_files:
                # 如果没有找到迁移文件，生成默认迁移
                return self._generate_default_migration(version)
            
            migration_file = migration_files[0]
            
            # 读取迁移文件内容
            with open(migration_file, 'r', encoding='utf-8') as f:
                content = f.read()
            
            # 简单解析迁移文件（实际项目中可能需要更复杂的解析）
            name = migration_file.stem
            
            return {
                "version": version,
                "name": name,
                "sql": content,
                "file": str(migration_file)
            }
            
        except Exception as e:
            self.logger.error(f"获取迁移信息失败 {version}: {e}")
            return None
    
    def _generate_default_migration(self, version: int) -> Dict[str, Any]:
        """生成默认迁移（用于初始化）"""
        if version == 1:
            # 初始迁移：创建基础表结构
            sql = """
            -- 初始化数据库结构
            -- 这个迁移由系统自动生成
            
            -- 视频表已通过SQLAlchemy模型创建
            -- 元数据表已通过SQLAlchemy模型创建
            -- 分类表已通过SQLAlchemy模型创建
            -- 任务表已通过SQLAlchemy模型创建
            -- 项目表已通过SQLAlchemy模型创建
            
            -- 创建一些初始索引（如果SQLAlchemy没有创建）
            CREATE INDEX IF NOT EXISTS idx_videos_file_path ON videos(file_path);
            CREATE INDEX IF NOT EXISTS idx_classifications_video_classifier ON video_classifications(video_id, classifier_type);
            CREATE INDEX IF NOT EXISTS idx_tasks_status_type ON processing_tasks(status, task_type);
            """
            
            return {
                "version": version,
                "name": "001_initial_migration",
                "sql": sql
            }

        elif version == 2:
            # 第二个迁移：创建剪映模板表
            sql = """
            -- 创建剪映模板表
            -- 这个迁移由系统自动生成

            -- 剪映模板表已通过SQLAlchemy模型创建
            -- 剪映视频片段表已通过SQLAlchemy模型创建

            -- 创建剪映模板相关索引
            CREATE INDEX IF NOT EXISTS idx_jianying_templates_template_id ON jianying_templates(template_id);
            CREATE INDEX IF NOT EXISTS idx_jianying_video_segments_template_id ON jianying_video_segments(template_id);
            CREATE INDEX IF NOT EXISTS idx_jianying_video_segments_material_id ON jianying_video_segments(material_id);
            CREATE INDEX IF NOT EXISTS idx_jianying_templates_canvas_ratio ON jianying_templates(canvas_ratio);
            CREATE INDEX IF NOT EXISTS idx_jianying_templates_duration ON jianying_templates(duration_seconds);
            """

            return {
                "version": version,
                "name": "002_add_jianying_templates",
                "sql": sql
            }

        return None
    
    def create_migration(self, name: str, sql: str) -> str:
        """创建新的迁移文件"""
        try:
            # 生成新的版本号
            new_version = self.target_version + 1
            
            # 生成文件名
            filename = f"{new_version:03d}_{name}.py"
            migration_file = self.migrations_dir / filename
            
            # 生成迁移文件内容
            migration_content = f'''"""
迁移: {name}
版本: {new_version}
创建时间: {datetime.now().isoformat()}
"""

# 迁移SQL
{sql}
'''
            
            # 写入文件
            with open(migration_file, 'w', encoding='utf-8') as f:
                f.write(migration_content)
            
            self.logger.info(f"迁移文件创建成功: {migration_file}")
            return str(migration_file)
            
        except Exception as e:
            self.logger.error(f"创建迁移文件失败: {e}")
            raise
    
    def rollback_migration(self, session, target_version: int) -> Dict[str, Any]:
        """回滚到指定版本"""
        try:
            applied_migrations = self.get_applied_migrations(session)
            current_version = max(applied_migrations) if applied_migrations else 0
            
            if target_version >= current_version:
                return {
                    "success": False,
                    "error": "目标版本不能大于或等于当前版本"
                }
            
            # 删除迁移记录
            session.execute(
                text("DELETE FROM schema_migrations WHERE version > :target_version"),
                {"target_version": target_version}
            )
            session.commit()
            
            self.logger.info(f"回滚到版本 {target_version}")
            
            return {
                "success": True,
                "from_version": current_version,
                "to_version": target_version
            }
            
        except Exception as e:
            session.rollback()
            self.logger.error(f"回滚迁移失败: {e}")
            return {
                "success": False,
                "error": str(e)
            }
    
    def get_migration_status(self, session) -> Dict[str, Any]:
        """获取迁移状态"""
        try:
            applied_migrations = self.get_applied_migrations(session)
            current_version = max(applied_migrations) if applied_migrations else 0
            
            pending_migrations = []
            for version in range(current_version + 1, self.target_version + 1):
                pending_migrations.append(version)
            
            return {
                "current_version": current_version,
                "target_version": self.target_version,
                "applied_migrations": applied_migrations,
                "pending_migrations": pending_migrations,
                "is_up_to_date": len(pending_migrations) == 0
            }
            
        except Exception as e:
            self.logger.error(f"获取迁移状态失败: {e}")
            return {
                "error": str(e),
                "current_version": 0,
                "target_version": self.target_version
            }
